/**
 * 
 */
package com.tientrung.qlsv.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.tientrung.qlsv.model.User;
import com.tientrung.qlsv.utils.Constants;

/**
 * @author MtRol
 * 
 */
public class DataAccess {
	private static DataAccess mInstance;
	private Connection mConnect;
	private Statement mStatement;

	public static DataAccess getInstance() {
		if (mInstance == null) {
			synchronized (DataAccess.class) {
				if (mInstance == null) {
					mInstance = new DataAccess();
				}
			}
		}

		return mInstance;
	}

	private DataAccess() {
		try {
			mConnect = DriverManager.getConnection(Constants.URL,
					Constants.USER, Constants.PASSWORD);
			mStatement = mConnect.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public User checkLogin(String user, String pass) {
		checkDataAccess();
//		int type = -1;
		User u = null;
		String sql = "Select * from user where UserName like '"
				+ user + "';";
		try {
			mStatement.executeQuery(sql);
			ResultSet set = mStatement.getResultSet();

			if (!set.next()) {
				System.out.println("Not found user!!!");
				return null;
			} else {
				if (set.getString("Password").equals(pass)) {
					u = new User();
					u.mid = set.getInt("id");
					u.mBirthDay = set.getLong("Birthday");
					u.mClass = set.getString("Class");
					u.mCountry = set.getString("Country");
					u.mMaSV = set.getString("Indentity");
					u.mName = set.getString("Name");
					u.mUserName = set.getString("UserName");
					u.mPassword = set.getString("Password");
					u.mType = set.getInt("type");
				} else {
					System.out.println("Password wrong!!!");
					return null;
				}
			}

			set.close();
			mStatement.close();
			mConnect.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return u;
	}

	public void showListSubject() {
		checkDataAccess();
		try {
			mStatement.executeQuery("select *from monhoc");
			ResultSet rs = mStatement.getResultSet();
			while (rs.next())
			{
				int idVal = rs.getInt("id");
				String nameVal = rs.getString("name");
				
				System.out.println("id: "+idVal+" ten mon hoc: "+nameVal);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		}

	/** show list subject cua user co id la @param id */
	public void showListSubject(int id) {
		checkDataAccess();
		String Sql = "select mamh,Name from monhoc join chonmon join user on user.id = chonmon.iduser and chonmon.idcours = monhoc.id and user.id ="+id+";";
		try {
			
			mStatement.executeQuery(Sql);
			ResultSet rs = mStatement.getResultSet();
			while (rs.next())
			{
				int maVal = rs.getInt("mamh");
				String NameVal = rs.getString("Name");
				
				System.out.println("Ma mon hoc: "+maVal+" ten mon hoc: "+NameVal);
			}
			
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	/** show list profile cua user co id la @param id */
	public void showProfile(int id) {

	}

	/** insert subject @param subjectId cho thang co id la @param userId */
	public void insertSubject(String userId, int subjectId) {
		checkDataAccess();
		String Sql ="insert into chonmon(iduser,idcours) value ('"+userId+"','"+subjectId+"');";
		try 
		{
			mStatement.executeUpdate(Sql);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	/** show danh sach user co type laf @param type */
	public void showListUser(int type) {

	}

	private void checkDataAccess() {
		try {
			if (mConnect.isClosed()) {
				mConnect = DriverManager.getConnection(Constants.URL,
						Constants.USER, Constants.PASSWORD);
			}
			if (mStatement.isClosed()) {
				mStatement = mConnect.createStatement();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
